#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
PRESTO_HOME=/export/server/presto/bin/presto
${HIVE_HOME} -S -e "
-- 客户意向宽表
create table edu_dwb.customer_relationship_detail_test
(
    --维度
    dt                  string  comment '天',             -- 时间
    area                string  comment '所在区域',        -- 地区
    itcast_school_id    int     comment '校区id',         -- 校区
    itcast_school_name  string  comment '校区名称',
    itcast_subject_id   int     comment '学科id',         -- 学科
    itcast_subject_name string  comment '学科名称',
    tdepart_id          int     comment '直属部门',        -- 咨询中心
    tdepart_name        string  comment '部门名称',
    origin_channel      string  comment '来源渠道',        -- 来源渠道
    origin_type         string  comment '数据来源（online-线上，offline-线下）',        -- 线上线下
    customer_state      string  comment '学员状态（new_customer-新学员，old_customer-老学员）',       -- 新老学员
    -- 指标
    id                  int     comment '意向id'
)
comment '客户意向事实表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;


-----------------------------------------------------------
-- 客户线索宽表
create table edu_dwb.customer_clue_detail_test (
-- 维度
hour            string    comment '小时',
customer_state  string    comment '学员状态（new_customer-新学员，old_customer-老学员）',    -- 新老学员
origin_type     string    comment '数据来源（online-线上，offline-线下）',                  -- 线上线下
-- 指标
id              int       comment '主键',
appeal_status   int       comment '申诉状态，0:待稽核 1:无效 2：有效'   -- 线索是否有效
)
comment '客户线索表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'snappy')
;
"

presto --catalog hive --server hadoop01:8090 --execute"
-- 插入数据到意向宽表  hive.edu_dwb.customer_relationship_detail
insert into hive.edu_dwb.customer_relationship_detail_test
select
substring(cr.create_date_time,1,10) as dt,
c.area,
cr.itcast_school_id,
sch.name as itcast_school_name,
cr.itcast_subject_id,
sub.name as itcast_subject_name,
case when d.name like '%咨询中心%' then e.tdepart_id
     else null end as tdepart_id,
case when d.name like '%咨询中心%' then d.name
     else null end as tdepart_name,
cr.origin_channel,
cr.origin_type,
cc.customer_state,
cr.id
from edu_dwd.fact_customer_relationship_test cr
left join edu_dwd.dim_customer_test c on cr.id = c.customer_relationship_id
left join edu_dwd.dim_itcast_school_test sch on cr.itcast_school_id = sch.id
left join edu_dwd.dim_itcast_subject_test sub on cr.itcast_subject_id = sub.id
left join edu_dwd.dim_employee_test e on cr.creator = e.id
left join edu_dwd.dim_scrm_department_test d on e.tdepart_id = d.id
left join edu_dwd.fact_customer_clue_test cc on cr.id = cc.customer_relationship_id
;


-- 插入数据到线索宽表  hive.edu_dwb.customer_clue_detail
insert into hive.edu_dwb.customer_clue_detail_test
select
substring(cr.create_date_time,1,13) as hour,
cc.customer_state,
cr.origin_type,
cc.id,
ca.appeal_status
from hive.edu_dwd.fact_customer_clue_test cc
left join hive.edu_dwd.fact_customer_relationship_test cr on cc.customer_relationship_id = cr.id
left join hive.edu_dwd.dim_customer_appeal_test ca on cc.customer_relationship_id = ca.customer_relationship_first_id
;
"